Hive

Hive DML、DDL、Load Data

Posted by Jackson on 2017-09-04

DDL

首先要创建表才能对Hive中的数据进行操作。

创建一个外部表：

1
2
3
4
5
6
7
8
9
10
CREATE EXTERNAL TABLE emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/bigdata_hive/hive_test/emp/';

加载数据到表中

1
LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.txt' into table emp;

DML

简单语法：

1
LOAD DATA LOCAL INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

有LOCAL： 代表的是从本地的Linux机器加载数据
无LOCAL： 代表的是从HDFS上面加载数据

Hive中有两种常见类型的表：
内部表 MANAGED TABLE
外部表 EXTERNAL TABLE

详细建表语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

CREATE TABLE emp2 LIKE emp; 仅仅复制表结构
CREATE TABLE emp3 as select * from emp; 表结构和表数据都复制

修改内部表为外部表

修改内部表为外部表的语句是：alter table emp set tblproperties('EXTERNAL'='TRUE')
注意：上面的EXTERNAL 和TRUE 为大写关键字

内部表和外部表的区别？

内部表删除时: HDFS + META 都被删除
外部表删除时: HDFS不删除 仅META被删除

分区表

1
2
3
4
5
6
create table order_partition(
order_no string,
event_time string
)
PARTITIONED BY (event_month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

注意：分区列并不是一个“真正的”表字段，其实是HDFS上表对应的文件夹下的一个文件夹

分区表加载数据一定要指定分区字段

1
LOAD DATA LOCAL INPATH '/home/hadoop/data/order.txt' INTO TABLE order_partition PARTITION (event_month='2017-09');

分区表查询的时候要加上分区的条件

在Hive中用SQL语句创建数据库的时候指定数据库在HDFS上面的路径位置，以后基于这个库的操作的时候，表默认都放到对应的这个目录下面。

对于分区表操作，如果你的数据是事先写入HDFS，然后再 默认sql是查询不到的，因为元数据里没有

生产上面不能执行msck repair table order_partition；这个操作是非常重量级的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
hive (bigdata_hive)> msck repair table order_partition;
OK
Partitions not in metastore:	order_partition:event_month=2017-10
Repair: Added partition to metastore order_partition:event_month=2017-10
Time taken: 0.224 seconds, Fetched: 2 row(s)
hive (bigdata_hive)> select * from order_partition where event_month='2017-10';
OK
order_partition.order_no	order_partition.event_time	order_partition.event_month
10703007267488	2014-05-01 06:01:12.334+01	2017-10
10101043505096	2014-05-01 07:28:12.342+01	2017-10
10103043509747	2014-05-01 07:50:12.33+01	2017-10
10103043501575	2014-05-01 09:27:12.33+01	2017-10
10104043514061	2014-05-01 09:03:12.324+01	2017-10
Time taken: 0.078 seconds, Fetched: 5 row(s)

生产上面可以使用下面的方式来进行操作：

1
ALTER TABLE order_partition ADD IF NOT EXISTS PARTITION(event_month='2017-11') ;
1
2
3
4
5
6
7
8
9
10
11
12
hive (bigdata_hive)> ALTER TABLE order_partition ADD IF NOT EXISTS PARTITION(event_month='2017-11') ;
OK
Time taken: 0.105 seconds
hive (bigdata_hive)> select * from order_partition where event_month='2017-11';
OK
order_partition.order_no	order_partition.event_time	order_partition.event_month
10703007267488	2014-05-01 06:01:12.334+01	2017-11
10101043505096	2014-05-01 07:28:12.342+01	2017-11
10103043509747	2014-05-01 07:50:12.33+01	2017-11
10103043501575	2014-05-01 09:27:12.33+01	2017-11
10104043514061	2014-05-01 09:03:12.324+01	2017-11
Time taken: 0.104 seconds, Fetched: 5 row(s)

使用show partitions tablename 来查看对应的分区的表名称

1
2
3
4
5
6
7
8
hive (bigdata_hive)> show partitions order_partition;
OK
partition
event_month=2017-09
event_month=2017-10
event_month=2017-11
Time taken: 0.092 seconds, Fetched: 3 row(s)
hive (bigdata_hive)>

多分区

生产上面一般使用的是多分区目录

1
2
3
4
5
6
create table order_mulit_partition(
order_no string,
event_time string
)
PARTITIONED BY (event_month string, step string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
1
LOAD DATA LOCAL INPATH '/home/hadoop/data/order.txt' INTO TABLE order_mulit_partition PARTITION (event_month='2014-05',step='0001');

使用分区表时，加载数据一定要指定我们的所有分区字段

静态分区

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double
) 
PARTITIONED BY (deptno int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

使用select语句插入数据的时候要注意，原来的emp表中有分区字段，所以这里需要指定具体的字段进行插入。

1
INSERT OVERWRITE TABLE emp_partition PARTITION (deptno=10) select empno,ename,job,mgr,hiredate,sal,comm   from emp where deptno=10;

动态分区

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE emp_dynamic_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double
) 
PARTITIONED BY (deptno int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
1
2
INSERT OVERWRITE TABLE emp_dynamic_partition PARTITION (deptno) select 
empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

在partition中是不指定值的，根据deptno来进行区分字段的值，上述的deptno字段一定要写在最后面。

静态分区和动态分区的区别在于加载数据时候分区字段的指定，静态分区加载直接指定分区字段，动态分区加载数据时候，是指定的分区的列。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
hive (bigdata_hive)> CREATE TABLE emp_dynamic_partition(
                   > empno int,
                   > ename string,
                   > job string,
                   > mgr int,
                   > hiredate string,
                   > sal double,
                   > comm double
                   > ) 
                   > PARTITIONED BY (deptno int)
                   > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.092 seconds
hive (bigdata_hive)> INSERT OVERWRITE TABLE emp_dynamic_partition PARTITION (deptno) select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict
hive (bigdata_hive)> set hive.exec.dynamic.partition.mode=nonstrict;
1
2
3
4
5
6
7
8
9
hive (bigdata_hive)> show partitions emp_dynamic_partition;
OK
partition
deptno=10
deptno=20
deptno=30
deptno=__HIVE_DEFAULT_PARTITION__
Time taken: 0.08 seconds, Fetched: 4 row(s)
hive (bigdata_hive)>

Hive 分桶表

概念：

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储路径；分桶针对的是数据文件。

分桶规则：对分桶字段值进行哈希，哈希值除以桶的个数求余，余数决定了该条记录在哪个桶中，也就是余数相同的在一个桶中。

优点：

  • 提高join查询效率
  • 提高抽样效率

创建一个分桶表：

1
2
3
4
5
6
7
CREATE TABLE bucket_table(
id int,
name string,
age string
)
clustered by (id) sorted by (id) into 4 buckets
ROW format delimited fields terminated by ',';
1
load data local inpath '/home/hadoop/data/bucket.txt' into table bucket_table

select * from emp tablesample (bucket 1 out of 2);

id name age
2 tom2 23
4 tom4 24
6 tom6 26
8 tom8 28

可以用sorted by (id) 来设置有序分桶表

1
2
3
4
5
6
7
CREATE TABLE bucket_sort(
id int,
name string,
age string
)
clustered by (id) sorted by (id) into 4 buckets
ROW format delimited fields terminated by ',';

分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

1
select * from test tablesample (bucket 1 out of 2 on id);

分桶表后面可以不带on 字段名，不带时默认的是按分桶字段,也可以带，而没有分桶的表则必须带
按分桶字段取样时，因为分桶表是直接去对应的桶中拿数据，在表比较大时会提高取样效率

1
tablesample (bucket x out of y on id);

x表示从哪个桶开始，y代表分几个桶，也可以理解分x为分子，y为分母，及将表分为y份（桶），取第x份（桶）

所以这时对于分桶表是有要求的，y为桶数的倍数或因子，

x=1,y=2，取2(4/y)个bucket的数据，分别桶1和桶3(1+y)

x=1,y=4, 取1(4/y)个bucket的数据，即桶1

x=2,y=8, 取1/2(4/y)个bucket的数据，即桶1的一半

x的值必须小于等于y的值

加载数据

LOAD DATA LOCAL INPATH '/home/hadoop/data/order_created.txt' [OVERWRITE] INTO TABLE order_mulit_partition PARTITION (event_month='2014-05',step='1');

LOAD DATA : 加载数据
LOCAL: “本地” 没有的话就HDFS
INPATH: 指定路径
OVERWRITE：数据覆盖 没有的话就是追加

从HDFS加载数据，如果是一个内部表，那么使用load的方式进行加载数据，就会把原来的数据移走，交给Hive管理，此时数据是存放在hive的表对应的数据目录

create table as select eno,ename from emp;

CTAS : create table … as select…
表不能事先存在

insert：
表必须事先存在

Fetch

Hive 0.14.0之后默认的fetch是more，Hive 0.14.0 之前的默认的fetch是minimal

none: Disable hive.fetch.task.conversion (value added in Hive 0.14.0 with HIVE-8389)
设置为none 时候所有任务都走MR

minimal: SELECT *, FILTER on partition columns (WHERE and HAVING clauses), LIMIT only
设置为minimal的时候 SELECT *, FILTER on partition columns (WHERE and HAVING clauses), LIMIT only 这些走MR

more: SELECT, FILTER, LIMIT only (including TABLESAMPLE, virtual columns)
设置为more的时候SELECT, FILTER, LIMIT 不会跑MR

Hive元数据信息查看

查看元数据信息的方式：

  • 1.去hive-site.xml 中找链接mysql对应的数据库名称
  • 2.到mysql中对应的数据库下查询表
  • 3.tbls 为普通的表 partitions为分区表对应的信息

安全模式报错处理及解决

1
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/tmp' SELECT * FROM emp;

安全模式报错：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
hive (bigdata_hive)> INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/tmp/emp.txt' SELECT * FROM emp;
FAILED: RuntimeException org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.hdfs.server.namenode.SafeModeException): Cannot create directory /tmp/hive/hadoop/25894946-03c1-4547-a249-1a01f8b0e606/hive_2019-12-16_22-19-09_624_6134799912934093237-1. Name node is in safe mode.
Resources are low on NN. Please add or free up more resources then turn off safe mode manually. NOTE:  If you turn off safe mode before adding resources, the NN will immediately return to safe mode. Use "hdfs dfsadmin -safemode leave" to turn safe mode off.
	at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkNameNodeSafeMode(FSNamesystem.java:1529)
	at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.mkdirsInt(FSNamesystem.java:4530)
	at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.mkdirs(FSNamesystem.java:4505)
	at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.mkdirs(NameNodeRpcServer.java:884)
	at org.apache.hadoop.hdfs.server.namenode.AuthorizationProviderProxyClientProtocol.mkdirs(AuthorizationProviderProxyClientProtocol.java:328)
	at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.mkdirs(ClientNamenodeProtocolServerSideTranslatorPB.java:641)
	at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod(ClientNamenodeProtocolProtos.java)
	at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:617)
	at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:1073)
	at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2278)
	at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2274)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:422)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1924)
	at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2272)

hive (bigdata_hive)> [hadoop@bigdata01 ~]$ 
[hadoop@bigdata01 ~]$

手动设置离开安全模式：

1
2
3
[hadoop@bigdata01 ~]$ hdfs dfsadmin -safemode leave
19/12/16 22:19:54 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Safe mode is OFF



FEATURED TAGS
Hive
FRIENDS